﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace NDBGen.Plugins
{
    /*public class SqlServerDataScriptGenerator : IDatabaseDataScriptGenerator
    {
        public String GenerateValueReferenceSql()
        {
            return String.Format("@{0}", this.Name);
        }

        public void WriteRecordLookupSql(String dbType, IIndentedTextWriter writer)
        {
            if (this.RequiresValueLookup)
            {
                writer.WriteLine(String.Format(
                    "declare @{0}_lookup {1};", this.Name, dbType));
                writer.WriteLine(String.Format(
                    "select @{0}_lookup = [{1}] from [{2}].[{3}] where [{4}] = @{5};",
                        this.Name,
                        this.ReferencedTableValueColumn,
                        this.ReferencedTableSchema,
                        this.ReferencedTableName,
                        this.ReferencedTableLookupColumn,
                        this.ParameterName));
                writer.WriteLineNoTabs(String.Empty);
            }
        }

        public void WriterBulkInsertSelectSql(String values, String where, String schemaName, String tableName, IIndentedTextWriter writer)
        {
            if (this.TriggersBulkInsert)
            {
                writer.WriteLine(String.Format(
                    "select {0} from [{2}].[{3}] where [{1}] not in (select [{4}] from [{5}].[{6}]{7});",
                        values,
                        this.ReferencedTableValueColumn,
                        this.ReferencedTableSchema,
                        this.ReferencedTableName,
                        this.Name,
                        schemaName,
                        tableName,
                        where));
            }
        }

        internal static Dictionary<Program.Arg, String> GenerateSql(List<Table> tables)
        {
            var schemaOutput = new List<String>();
            foreach (var table in tables) schemaOutput.Add(table.GenerateSql());

            var lookupOutput = new List<String>();
            foreach (var table in tables)
            {
                lookupOutput.AddRange(table.GenerateSqlDefineProcedure(null, table.ScriptedData));
                lookupOutput.AddRange(table.GenerateDefineData(null, table.ScriptedData));
            }

            var seedOutput = new List<String>();
            foreach (var table in tables)
            {
                seedOutput.AddRange(table.GenerateSqlDefineProcedure("_seed", table.SeedData));
                seedOutput.AddRange(table.GenerateDefineData("_seed", table.SeedData));
            }

            var fakeOutput = new List<String>();
            foreach (var table in tables)
            {
                fakeOutput.AddRange(table.GenerateSqlDefineProcedure("_fake", table.FakeData));
                fakeOutput.AddRange(table.GenerateDefineData("_fake", table.FakeData));
            }

            var result = new Dictionary<Program.Arg, String>();
            schemaOutput.RemoveAll(l => String.IsNullOrEmpty(l));
            lookupOutput.RemoveAll(l => String.IsNullOrEmpty(l));
            seedOutput.RemoveAll(l => String.IsNullOrEmpty(l));
            fakeOutput.RemoveAll(l => String.IsNullOrEmpty(l));

            if (lookupOutput.Any())
            {
                if (Program.config.ContainsKey(Program.Arg.LookupDataOutput)) result.Add(Program.Arg.LookupDataOutput, lookupOutput.CombineLines());
                else schemaOutput.AddRange(lookupOutput);
            }

            if (seedOutput.Any())
            {
                if (Program.config.ContainsKey(Program.Arg.SeedDataOutput)) result.Add(Program.Arg.SeedDataOutput, seedOutput.CombineLines());
                else schemaOutput.AddRange(seedOutput);
            }

            if (fakeOutput.Any())
            {
                if (Program.config.ContainsKey(Program.Arg.FakeDataOutput)) result.Add(Program.Arg.FakeDataOutput, fakeOutput.CombineLines());
                else schemaOutput.AddRange(fakeOutput);
            }

            result.Add(Program.Arg.SchemaSqlOutput, schemaOutput.CombineLines());

            return result;
        }

        public String GenerateSql()
        {
            using (var writer = Generator.CreateWriter())
            {
                writer.WriteLine(String.Format(
                    "if not exists (select * from sys.schemas where name = N'{0}')", this.Schema));
                writer.WriteLine(String.Format(
                    "exec sp_executesql N'create schema [{0}] authorization [dbo]'", this.Schema));
                writer.WriteLine(
                    "go");
                writer.WriteLine(String.Format(
                    "if not exists (select * from sys.objects where object_id = object_id(N'[{0}].[{1}]') and type in (N'U'))", this.Schema, this.Name));
                writer.WriteLine(String.Format(
                    "create table [{0}].[{1}](", this.Schema, this.Name));
                writer.Indent++;
                foreach (var col in this.Columns) col.GenerateSql(writer);
                writer.Indent--;
                writer.WriteLine(String.Format(
                    " constraint [PK_{0}] primary key clustered (", this.Name));
                writer.Indent++;
                foreach (var col in this.Columns.Where(c => c.HasPrimaryKey))
                {
                    writer.WriteLine("[" + col.Name + "] asc");
                }
                writer.Indent--;
                writer.WriteLine(
                    ") with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [default] ");
                writer.WriteLine(
                    ") on [default]");
                writer.WriteLine(
                    "go");

                return ((StringWriter)writer.InnerWriter).ToString();
            }
        }

        public static String GenerateObliterate(List<Table> tables)
        {
            tables.Reverse();
            var output = new List<String>();
            foreach (var table in tables) output.Add(table.GenerateDropSql());
            return output.CombineLines();
        }

        public void GenerateColumn(Column column, IIndentedTextWriter writer)
        {
            writer.Write(column.DataTypeHandler.GenerateSql());

            var orderedMetadata = column.TokenMetadataList.SortOrdered();

            foreach (var descriptor in orderedMetadata)
            {
                var sql = descriptor.GenerateSql();
                if (!String.IsNullOrEmpty(sql))
                {
                    writer.Write(' ');
                    writer.Write(sql);
                }
            }

            writer.Write(',');
            writer.WriteLine();
        }

        public String[] GenerateSqlDefineProcedure(String prefix, params ScriptedTableData[] dataEntries)
        {
            if (dataEntries == null) return new String[0];
            var result = new List<String>();
            foreach (var data in dataEntries)
            {
                if (data == null || !data.Entries.Any(e => e.Values.Any()) || !data.Entries.Any(e => e.Values.Any(v => !String.IsNullOrEmpty(v)))) continue;
                else
                {
                    if (this.Columns == null) throw new NullReferenceException(String.Format("Table.Columns was null while generating SQL define{2} procedure for {0}.{1}", this.Schema, this.Name, prefix));
                    if (data.Columns == null) throw new NullReferenceException(String.Format("data.Columns was null while generating SQL define{2} procedure for {0}.{1}", this.Schema, this.Name, prefix));

                    var allColumns = (
                        from c in this.Columns
                        join lc in data.Columns on c.Name equals lc.Name into lookupColumns
                        from lc in lookupColumns.DefaultIfEmpty()
                        // this is grossly inefficient, but it's the easiest way to maintain the
                        // original order of data.Columns
                        orderby data.Columns.ToList().IndexOf(lc)
                        select new
                        {
                            Name = c.Name,
                            Length = c.Length,
                            Type = c.Type,
                            TokenMetadata = c.TokenMetadataList,
                            ScriptedData = lc,
                            DbType = String.Format("{0}{1}",
                                c.Type.ToString().Replace("Max", "(max)").ToLower(),
                                c.Length.HasValue ? String.Format("({0})", c.Length) : String.Empty)
                        }).ToArray();

                    if (allColumns.Count(c => c.ScriptedData != null && c.ScriptedData.TriggersBulkInsert) > 1) throw new InvalidDataException(String.Format("Table {0}.{1} defines multiple columns that trigger a bulk insert operation. Only one such column is allowed.", this.Schema, this.Name));

                    try
                    {
                        var autoGeneratedIdColumn = allColumns.SingleOrDefault(c =>
                            c.TokenMetadata.Any(t => t.GetType() == typeof(PrimaryKeyToken)) &&
                            c.TokenMetadata.Any(t => t.GetType() == typeof(AutoGeneratedTokenDescriptor)));

                        var columns = allColumns.Where(c => c.ScriptedData != null && !c.TokenMetadata.Any(m => m.GetType() == typeof(AutoGeneratedTokenDescriptor))).ToArray();
                        var idColumn = columns.SingleOrDefault(c => c.TokenMetadata.Any(t => t.GetType() == typeof(PrimaryKeyToken)));
                        var lookupSourceColumns = columns.Where(c => c.ScriptedData != null && c.ScriptedData.IsLookupKey);
                        var nonAutoGeneratedIdColumns = columns.Where(c => c != autoGeneratedIdColumn).ToArray();
                        var bulkInsertTrigger = allColumns.SingleOrDefault(c => c.ScriptedData != null && c.ScriptedData.TriggersBulkInsert);
                        var bulkInsertSuffix = bulkInsertTrigger == null ? String.Empty : bulkInsertTrigger.ScriptedData.BulkInsertSuffix;

                        using (var writer = Generator.CreateWriter())
                        {
                            writer.WriteLine(String.Format(
                                "if not exists (select * from sys.procedures where object_id = object_id(N'[{0}].[define{2}_{1}{3}]'))", this.Schema, this.Name.Depluralize(), prefix ?? String.Empty, bulkInsertSuffix));
                            writer.Indent++;
                            writer.WriteLine(String.Format(
                                "exec sp_executesql N'create procedure [{0}].[define{2}_{1}{3}] as begin return 0; end';", this.Schema, this.Name.Depluralize(), prefix ?? String.Empty, bulkInsertSuffix));
                            writer.Indent--;
                            writer.WriteLine(
                                "go");
                            writer.WriteLine(String.Format(
                                "alter procedure [{0}].[define{2}_{1}{3}] (", this.Schema, this.Name.Depluralize(), prefix ?? String.Empty, bulkInsertSuffix));
                            writer.Indent++;
                            foreach (var column in nonAutoGeneratedIdColumns.Where(c => !c.ScriptedData.TriggersBulkInsert))
                            {
                                if (column.ScriptedData.RequiresValueLookup)
                                {
                                    writer.Write("@{0} {1}", column.ScriptedData.ParameterName, column.ScriptedData.GetParameterType(this.Columns));
                                }
                                else if (!column.ScriptedData.TriggersBulkInsert)
                                {
                                    writer.Write("@{0} {1}", column.ScriptedData.Name, column.DbType);
                                }
                                if (column != nonAutoGeneratedIdColumns.Last(c => !c.ScriptedData.TriggersBulkInsert)) writer.Write(",");
                                writer.WriteLine();
                            }
                            writer.Indent--;
                            writer.WriteLine(
                                ")");
                            writer.WriteLine(
                                "as");
                            writer.WriteLine(
                                "begin");
                            writer.Indent++;

                            foreach (var column in columns)
                            {
                                column.ScriptedData.WriteRecordLookupSql(column.DbType, writer);
                            }

                            if (autoGeneratedIdColumn == null && !lookupSourceColumns.Any())
                                throw new InvalidOperationException(String.Format("The table {0}.{1} must have an auto-generated ID or the the lookup definition must specify a lookup column.", this.Schema, this.Name));

                            if (bulkInsertTrigger == null && columns.Length > 1 && lookupSourceColumns.Any())
                            {
                                writer.Write(
                                    "if not exists (select * from [{0}].[{1}] where", this.Schema, this.Name);
                                foreach (var lookupColumn in lookupSourceColumns)
                                {
                                    if (lookupColumn != lookupSourceColumns.First())
                                    {
                                        writer.Write(" and");
                                    }

                                    var isNullable = lookupColumn.TokenMetadata.Any(m => m.GetType() == typeof(NullableToken) && ((NullableToken)m).IsActive);
                                    if (isNullable)
                                    {
                                        writer.Write(" (([{0}] is null and @{0}{1} is null) or", lookupColumn.Name, lookupColumn.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);
                                    }

                                    writer.Write(" [{0}] = @{0}{1}", lookupColumn.Name, lookupColumn.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);

                                    if (isNullable)
                                    {
                                        writer.Write(")");
                                    }
                                }
                                writer.WriteLine(")");
                                writer.Indent++;
                            }

                            writer.Write(String.Format(
                                "insert [{0}].[{1}] (", this.Schema, this.Name));
                            foreach (var column in columns)
                            {
                                writer.Write("[{0}]", column.Name);
                                if (column != columns.Last()) writer.Write(",");
                            }
                            writer.Write(")");
                            writer.WriteLine();
                            if (bulkInsertTrigger == null)
                            {
                                writer.Write(
                                    "values (");
                                foreach (var column in columns)
                                {
                                    writer.Write("@{0}{1}", column.Name, column.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);
                                    if (column != columns.Last()) writer.Write(",");
                                }
                                writer.Write(");");
                            }
                            else
                            {
                                var values = new StringBuilder();
                                foreach (var column in nonAutoGeneratedIdColumns)
                                {
                                    if (column == bulkInsertTrigger)
                                    {
                                        values.AppendFormat("[{0}]", column.ScriptedData.ReferencedTableValueColumn);
                                    }
                                    else
                                    {
                                        values.AppendFormat("@{0}{1}", column.Name, column.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);
                                    }
                                    if (column != nonAutoGeneratedIdColumns.Last()) values.AppendFormat(",");
                                }

                                var where = new StringBuilder();
                                if (lookupSourceColumns.Any())
                                {
                                    var whereColumns = lookupSourceColumns.Where(c => c != bulkInsertTrigger);
                                    where.Append(" where");
                                    foreach (var lookupColumn in whereColumns)
                                    {
                                        if (lookupColumn != whereColumns.First())
                                        {
                                            where.Append(" and");
                                        }

                                        var isNullable = lookupColumn.TokenMetadata.Any(m => m.GetType() == typeof(NullableToken) && ((NullableToken)m).IsActive);
                                        if (isNullable)
                                        {
                                            where.AppendFormat(" (([{0}] is null and @{0}{1} is null) or", lookupColumn.Name, lookupColumn.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);
                                        }

                                        where.AppendFormat(" [{0}] = @{0}{1}", lookupColumn.Name, lookupColumn.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);

                                        if (isNullable)
                                        {
                                            where.AppendFormat(")");
                                        }
                                    }
                                }
                                bulkInsertTrigger.ScriptedData.WriterBulkInsertSelectSql(values.ToString(), where.ToString(), this.Schema, this.Name, writer);
                            }
                            if (columns.Length > 1 && lookupSourceColumns.Any()) writer.Indent--;
                            writer.WriteLine();

                            if (bulkInsertTrigger == null && columns.Length > 1 && lookupSourceColumns.Any() && columns.Any(c => !lookupSourceColumns.Contains(c)))
                            {
                                writer.WriteLine("else");
                                writer.Indent++;
                                writer.WriteLine(
                                    "update [{0}].[{1}] set", this.Schema, this.Name);
                                writer.Indent++;
                                foreach (var column in columns)
                                {
                                    if (!lookupSourceColumns.Contains(column))
                                    {
                                        writer.Write(
                                            "[{0}] = @{0}{1}", column.Name, column.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);
                                        if (column != columns.Where(c => !lookupSourceColumns.Contains(c) && c != idColumn).Last())
                                            writer.Write(",");
                                        writer.WriteLine();
                                    }
                                }
                                writer.Indent--;
                                writer.Write(
                                    "where");
                                foreach (var lookupColumn in lookupSourceColumns)
                                {
                                    if (lookupColumn != lookupSourceColumns.First())
                                    {
                                        writer.Write(" and");
                                    }

                                    var isNullable = lookupColumn.TokenMetadata.Any(m => m.GetType() == typeof(NullableToken) && ((NullableToken)m).IsActive);
                                    if (isNullable)
                                    {
                                        writer.Write(" (([{0}] is null and @{0}{1} is null) or", lookupColumn.Name, lookupColumn.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);
                                    }

                                    writer.Write(" [{0}] = @{0}{1}", lookupColumn.Name, lookupColumn.ScriptedData.RequiresValueLookup ? "_lookup" : String.Empty);

                                    if (isNullable)
                                    {
                                        writer.Write(")");
                                    }
                                }
                                writer.WriteLine(";");
                                writer.Indent--;
                            }

                            writer.WriteLineNoTabs(String.Empty);
                            writer.WriteLine(
                                "return 0;");

                            writer.Indent--;
                            writer.WriteLine(
                                "end");
                            writer.WriteLine(
                                "go");
                            result.Add(((StringWriter)writer.InnerWriter).ToString());
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(String.Format("Error generating SQL define{2} procedure for {0}.{1}", this.Schema, this.Name, prefix), ex);
                    }
                }
            }
            return result.ToArray();
        }

        public String[] GenerateDefineData(String prefix, params ScriptedTableData[] dataEntries)
        {
            if (dataEntries == null) return new String[0];
            var result = new List<String>();
            foreach (var data in dataEntries)
            {
                if (data == null || !data.Entries.Any(e => e.Values.Any())) continue;
                else
                {
                    using (var writer = Generator.CreateWriter())
                    {
                        var columns = (
                            from lc in data.Columns
                            join c in this.Columns on lc.Name equals c.Name into cols
                            from c in cols.DefaultIfEmpty()
                            select new
                            {
                                Name = lc.Name,
                                ParameterName = lc.ParameterName,
                                DataType = c == null ? default(DataType) : c.Type,
                                TriggersBulkInsert = lc.TriggersBulkInsert,
                                BulkInsertSuffix = lc.BulkInsertSuffix,
                                RequiresValueLookup = lc.RequiresValueLookup,
                                ReferencedTableSchema = lc.ReferencedTableSchema,
                                ReferencedTableName = lc.ReferencedTableName,
                                ReferencedTableValueColumn = lc.ReferencedTableValueColumn,
                                Type = c == null ? DataType.NVarCharMax : c.Type,
                                IsNullable = c == null ? false : c.TokenMetadataList.Any(t => t.GetType() == typeof(NullableToken))
                            }).ToArray();

                        var bulkInsertTrigger = columns.SingleOrDefault(c => c.TriggersBulkInsert);
                        var bulkInsertSuffix = bulkInsertTrigger == null ? String.Empty : bulkInsertTrigger.BulkInsertSuffix;

                        String[] previousValues = new String[data.Columns.Length];
                        foreach (var entry in data.Entries)
                        {
                            foreach (var convColumn in columns.Where(c => c.DataType.RequiresConvert()))
                            {
                                writer.WriteLine("declare @{0}_{1} {2};", convColumn.Name, entry.GetHashCode(), convColumn.DataType.ToString().ToLower());
                                writer.WriteLine("select @{0}_{1} = cast('{2}' as {3});", convColumn.Name, entry.GetHashCode(), entry.Values[Array.IndexOf(columns, convColumn)], convColumn.DataType.ToString().ToLower());
                            }

                            writer.Write("exec [{0}].[define{2}_{1}{3}] ", this.Schema, this.Name.Depluralize(), prefix ?? String.Empty, bulkInsertSuffix);
                            var paramsWritten = 0;
                            for (var index = 0; index < columns.Length; index++)
                            {
                                if (index >= columns.Length) throw new ArgumentException(String.Format("The lookup data for {0}.{1} specifies more value indexes than columns", this.Schema, this.Name));
                                var column = columns[index];

                                var value = index < entry.Values.Length ? entry.Values[index] : (String)null;

                                if (column.TriggersBulkInsert) continue;

                                if (value == "^") value = previousValues[index];
                                else previousValues[index] = value;

                                if (column.Type == DataType.Bit)
                                {
                                    var b = Boolean.Parse(value);
                                    value = Convert.ToByte(b).ToString();
                                }

                                if (paramsWritten > 0) writer.Write(',');

                                writer.Write("@{0}=", column.RequiresValueLookup ? column.ParameterName : column.Name);

                                if (String.IsNullOrEmpty(value))
                                {
                                    if (column.IsNullable) writer.Write("null");
                                    else if (column.Type.IsStringType()) writer.Write("''");
                                    else writer.Write("0");
                                }
                                else
                                {
                                    if (!column.DataType.RequiresConvert() && (column.RequiresValueLookup || column.Type.RequiresQuotes())) writer.Write("'");
                                    if (column.DataType.RequiresConvert()) writer.Write("@{0}_{1}", column.Name, entry.GetHashCode());
                                    else writer.Write(value.Replace("'", "''"));
                                    if (!column.DataType.RequiresConvert() && (column.RequiresValueLookup || column.Type.RequiresQuotes())) writer.Write("'");
                                    // note: column.DataType.ToString() will have to be replaced by some smarter logic if this needs to be done for any sized data type like varchar(50), etc
                                }
                                paramsWritten++;
                            }
                            writer.Write(';');
                            writer.WriteLine();
                        }
                        writer.WriteLine(
                            "go");
                        result.Add(((StringWriter)writer.InnerWriter).ToString());
                    }
                }
            }
            return result.ToArray();
        }

        public String GenerateDropSql()
        {
            var sb = new StringBuilder();

            sb.AppendLine(String.Format(
                "if exists (select * from sys.objects WHERE object_id = object_id(N'[{0}].[{1}]') AND type in (N'U'))", this.Schema, this.Name));
            sb.AppendLine(String.Format(
                "drop table [{0}].[{1}];", this.Schema, this.Name));
            sb.AppendLine(
                "go");
            return sb.ToString();
        }
    }*/
}
